package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.fact.dto.MakeDTO;
import com.fact.dto.RoleDTO;
import com.fact.utils.connection.ConnectionManager;


public class RoleService {
	
	public ArrayList<RoleDTO> getRoleList()
	{
		RoleDTO role=null;
		ArrayList<RoleDTO> roleList=new ArrayList<RoleDTO>();
		Connection conn = ConnectionManager.getConnection();
		   String sql = " Select * from MST_ROLE Order by ROLE_ID";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
			    
				r = stmt.executeQuery();
				while (r.next())
				{
					role=new RoleDTO();
					role.setRoleId(r.getInt("ROLE_ID"));
					role.setRoleName(r.getString("ROLE_NAME"));
					roleList.add(role);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return roleList;
	}
	
	public boolean saveRole(RoleDTO	role)
	{

		Connection conn = ConnectionManager.getConnection();
		String sql=" Insert into MST_ROLE(ROLE_ID,ROLE_NAME) " +
				   " values(SQN_ROLE.nextval,?)";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,role.getRoleName());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 	if(response==1)
	 		return true;
	 	else
	 		return false;
	}
	
	public boolean deleteRole(int roleId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete MST_ROLE Where ROLE_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,roleId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public boolean editRole(RoleDTO role)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update MST_ROLE  set ROLE_NAME=?  Where ROLE_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,role.getRoleName());
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public RoleDTO getRole(int roleId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * MST_ROLE FROM  Where ROLE_ID=?";
		PreparedStatement stmt = null;
		RoleDTO  role=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setInt(1,roleId);
				r = stmt.executeQuery();
				if (r.next())
				{
					role=new RoleDTO();
					role.setRoleId(r.getInt("ROLE_ID"));
					role.setRoleName(r.getString("ROLE_NAME"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return role;
	}
	
	public RoleDTO getRole(String roleName)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * FROM MST_ROLE  Where lower(ROLE_NAME)=lower(?)";
		PreparedStatement stmt = null;
		RoleDTO  role=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,roleName);
				r = stmt.executeQuery();
				if (r.next())
				{
					role=new RoleDTO();
					role.setRoleId(r.getInt("ROLE_ID"));
					role.setRoleName(r.getString("ROLE_NAME"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return role;
	}

}
